import 'dart:io';
import 'package:flutter/foundation.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import '../models/hash_file_record.dart';

/// 文件哈希数据库服务
/// 负责管理本地SQLite数据库，存储文件路径和哈希值的映射关系
class HashDatabaseService {
  // 单例模式
  static final HashDatabaseService _instance = HashDatabaseService._internal();
  factory HashDatabaseService() => _instance;
  HashDatabaseService._internal();

  // 数据库实例
  Database? _database;

  // 数据库名称和版本
  static const String _databaseName = 'file_hash.db';
  static const int _databaseVersion = 1;

  // 表名
  static const String _tableName = 'hash_file_records';

  /// 获取数据库实例（如果不存在则创建）
  Future<Database> get database async {
    if (_database != null) return _database!;

    _database = await _initDatabase();
    return _database!;
  }

  /// 初始化数据库
  Future<Database> _initDatabase() async {
    try {
      // 获取数据库文件路径
      final documentsDirectory = await getApplicationDocumentsDirectory();
      final path = join(documentsDirectory.path, _databaseName);

      debugPrint('初始化哈希数据库，路径: $path');

      // 打开数据库
      return await openDatabase(
        path,
        version: _databaseVersion,
        onCreate: (db, version) async {
          // 创建表结构
          await HashFileRecord.createTable(db);
          debugPrint('成功创建文件哈希记录表');
        },
        onUpgrade: (db, oldVersion, newVersion) async {
          // 数据库升级逻辑（后续版本可能需要）
          debugPrint('数据库升级: $oldVersion -> $newVersion');
        },
      );
    } catch (e) {
      debugPrint('初始化数据库失败: $e');
      rethrow;
    }
  }

  /// 关闭数据库连接
  Future<void> close() async {
    final db = _database;
    if (db != null) {
      await db.close();
      _database = null;
      debugPrint('已关闭数据库连接');
    }
  }

  /// 保存文件哈希记录
  /// 如果文件路径已存在，则更新哈希值
  Future<int> saveHashRecord(HashFileRecord record) async {
    try {
      final db = await database;

      // 检查是否已存在相同文件路径的记录
      final existingRecord = await getRecordByPath(record.filePath);

      if (existingRecord != null) {
        // 更新现有记录
        record.id = existingRecord.id;
        await db.update(
          _tableName,
          record.toMap(),
          where: 'id = ?',
          whereArgs: [record.id],
        );
        debugPrint('已更新文件哈希记录: ${record.filePath}');
        return record.id!;
      } else {
        // 插入新记录
        final id = await db.insert(_tableName, record.toMap());
        debugPrint('已保存新文件哈希记录: ${record.filePath}');
        return id;
      }
    } catch (e) {
      debugPrint('保存哈希记录失败: $e');
      rethrow;
    }
  }

  /// 批量保存哈希记录
  Future<void> saveHashRecords(List<HashFileRecord> records) async {
    try {
      final db = await database;

      // 开始事务
      await db.transaction((txn) async {
        for (var record in records) {
          // 检查是否已存在相同文件路径的记录
          final existingRecord = await getRecordByPath(record.filePath);

          if (existingRecord != null) {
            // 更新现有记录
            record.id = existingRecord.id;
            await txn.update(
              _tableName,
              record.toMap(),
              where: 'id = ?',
              whereArgs: [record.id],
            );
          } else {
            // 插入新记录
            await txn.insert(_tableName, record.toMap());
          }
        }
      });

      debugPrint('已批量保存${records.length}个哈希记录');
    } catch (e) {
      debugPrint('批量保存哈希记录失败: $e');
      rethrow;
    }
  }

  /// 根据文件路径获取哈希记录
  Future<HashFileRecord?> getRecordByPath(String filePath) async {
    try {
      final db = await database;

      final results = await db.query(
        _tableName,
        where: 'file_path = ?',
        whereArgs: [filePath],
      );

      if (results.isNotEmpty) {
        return HashFileRecord.fromMap(results.first);
      }

      return null;
    } catch (e) {
      debugPrint('查询哈希记录失败: $e');
      rethrow;
    }
  }

  /// 根据哈希值获取哈希记录
  Future<List<HashFileRecord>> getRecordsByHash(String hash) async {
    try {
      final db = await database;

      final results = await db.query(
        _tableName,
        where: 'hash = ?',
        whereArgs: [hash],
      );

      return results.map((map) => HashFileRecord.fromMap(map)).toList();
    } catch (e) {
      debugPrint('查询哈希记录失败: $e');
      rethrow;
    }
  }

  /// 检查哈希值是否存在
  Future<bool> isHashExists(String hash) async {
    try {
      final db = await database;

      final results = await db.query(
        _tableName,
        columns: ['id'],
        where: 'hash = ?',
        whereArgs: [hash],
        limit: 1,
      );

      return results.isNotEmpty;
    } catch (e) {
      debugPrint('检查哈希值是否存在失败: $e');
      rethrow;
    }
  }

  /// 标记文件为已上传
  Future<void> markAsUploaded(String filePath) async {
    try {
      final db = await database;

      final record = await getRecordByPath(filePath);
      if (record != null) {
        record.markAsUploaded();

        await db.update(
          _tableName,
          record.toMap(),
          where: 'id = ?',
          whereArgs: [record.id],
        );

        debugPrint('已标记文件为已上传: $filePath');
      }
    } catch (e) {
      debugPrint('标记文件为已上传失败: $e');
      rethrow;
    }
  }

  /// 获取所有已上传的文件哈希值
  Future<List<String>> getAllUploadedHashes() async {
    try {
      final db = await database;

      final results = await db.query(
        _tableName,
        columns: ['hash'],
        where: 'uploaded_at IS NOT NULL',
      );

      return results.map((map) => map['hash'] as String).toList();
    } catch (e) {
      debugPrint('获取已上传哈希列表失败: $e');
      rethrow;
    }
  }

  /// 获取记录总数
  Future<int> getRecordCount() async {
    try {
      final db = await database;

      final results = await db.rawQuery(
        'SELECT COUNT(*) as count FROM $_tableName',
      );
      final count = Sqflite.firstIntValue(results) ?? 0;

      return count;
    } catch (e) {
      debugPrint('获取记录总数失败: $e');
      return 0;
    }
  }

  /// 清除所有哈希记录
  Future<void> clearAllRecords() async {
    try {
      final db = await database;

      // 删除表中所有记录
      final count = await db.delete(_tableName);

      debugPrint('已清除数据库中$count条哈希记录');
    } catch (e) {
      debugPrint('清除所有哈希记录失败: $e');
      rethrow;
    }
  }

  /// 删除过期的记录（例如90天以上未上传的记录）
  Future<int> deleteExpiredRecords(int days) async {
    try {
      final db = await database;

      // 计算过期时间戳
      final expiryTimestamp =
          DateTime.now().subtract(Duration(days: days)).millisecondsSinceEpoch;

      // 删除过期且未上传的记录
      final count = await db.delete(
        _tableName,
        where: 'created_at < ? AND uploaded_at IS NULL',
        whereArgs: [expiryTimestamp],
      );

      debugPrint('已删除$count条过期哈希记录');
      return count;
    } catch (e) {
      debugPrint('删除过期记录失败: $e');
      return 0;
    }
  }

  /// 更新已存在的哈希记录
  Future<void> updateHashRecord(HashFileRecord record) async {
    try {
      if (record.id == null) {
        throw Exception('更新记录需要有效的id');
      }

      final db = await database;

      await db.update(
        _tableName,
        record.toMap(),
        where: 'id = ?',
        whereArgs: [record.id],
      );

      debugPrint('已更新哈希记录(ID=${record.id}): ${record.filePath}');
    } catch (e) {
      debugPrint('更新哈希记录失败: $e');
      rethrow;
    }
  }
}
